﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GroupManager.Resource.CSDL;
using System.Data;
namespace GroupManager.Class
{
    public class LopThanhVien
    {
        // Kiểm tra thành viên tồn tại;
        public static bool KiemTraThanhVienTonTai(string masv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            if (db.ThanhViens.Where(q => q.MaSV == masv).Count() == 1)
                return true;
            return false;
        }

        // Kiểm tra tồn tại đội nhóm ;
        public static bool KiemTraDoiNhomTonTai(int madnhom)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            if (db.DoiNhoms.Where(q => q.MaDoiNhom == madnhom) != null)
                return true;
            return false;
        }

        // Thêm thành viên;
        public static void ThemThanhVien(string masv, string tensv, int ngay, int thang, int nam, bool gt, string lop,
                           string khoa, string truong, string sdt, string email, string mau, string sotruong, string sodoan,
                           string hoancanh, string ghichu, int tinhtrang, string ngaytgia, int qq, string anh, string face)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien tv = new ThanhVien();
            tv.MaSV = masv;
            tv.HoTen = tensv;
            tv.NgaySinh = ngay;
            tv.ThangSinh = thang;
            tv.NamSinh = nam;
            tv.GioiTinh = gt;
            tv.Lop = lop;
            tv.Khoa = khoa;
            tv.Truong = truong;
            tv.SoDT = sdt;
            tv.Email = email;
            tv.NhomMau = mau;
            tv.SoTruong = sotruong;
            tv.SoDoan = sodoan;
            tv.HoanCanh = hoancanh;
            tv.GhiChu = ghichu;
            tv.TinhTrang = tinhtrang;
            tv.NgayThamGia = Convert.ToDateTime(ngaytgia);
            tv.QueQuan = qq;
            tv.Anh = anh;
            tv.Facebook = face;
            db.ThanhViens.InsertOnSubmit(tv);
            db.SubmitChanges();
        }

        // Sửa thành viên;
        public static void SuaThanhVien(string masv, string tensv, int ngay, int thang, int nam, bool gt, string lop,
                           string khoa, string truong, string sdt, string email, string mau, string sotruong, string sodoan,
                           string hoancanh, string ghichu, int tinhtrang, string ngaytgia, int qq, string anh, string face)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien tv = db.ThanhViens.SingleOrDefault(q=>q.MaSV==masv);
            tv.MaSV = masv;
            tv.HoTen = tensv;
            tv.NgaySinh = ngay;
            tv.ThangSinh = thang;
            tv.NamSinh = nam;
            tv.GioiTinh = gt;
            tv.Lop = lop;
            tv.Khoa = khoa;
            tv.Truong = truong;
            tv.SoDT = sdt;
            tv.Email = email;
            tv.NhomMau = mau;
            tv.SoTruong = sotruong;
            tv.SoDoan = sodoan;
            tv.HoanCanh = hoancanh;
            tv.GhiChu = ghichu;
            tv.TinhTrang = tinhtrang;
            tv.NgayThamGia = Convert.ToDateTime(ngaytgia);
            tv.QueQuan = qq;
            tv.Anh = anh;
            tv.Facebook = face;
            db.SubmitChanges();
        }

        // Xóa thành viên;
        public static void XoaThanhVien(string masv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien tv = db.ThanhViens.SingleOrDefault(q => q.MaSV == masv);
            db.ThanhViens.DeleteOnSubmit(tv);
            db.SubmitChanges();
        }

        // Thêm, xóa vào bảng ThanhVien_Ban;
        public static void ThemThanhVienBan(string masv, int maban, string chucvu)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien_Ban tvb = new ThanhVien_Ban();
            tvb.MaBan = maban;
            tvb.MaSV = masv;
            tvb.ChucVu = chucvu;
            db.ThanhVien_Bans.InsertOnSubmit(tvb);
            db.SubmitChanges();
        }

        public static void XoaThanhVienBan(string masv, int maban)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = from tvban in db.ThanhVien_Bans
                        where tvban.MaSV == masv && tvban.MaBan == maban
                        select tvban;
            if (sql.FirstOrDefault() != null)
            {
                db.ThanhVien_Bans.DeleteOnSubmit(sql.First());
                db.SubmitChanges();
            }
        }

        // Thêm, xóa vào bảng ThanhVien_DoiNhom;
        public static void ThemThanhVienDoiNhom(string masv, int madnhom)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien_DoiNhom tvdnhom = new ThanhVien_DoiNhom();
            tvdnhom.MaSV = masv;
            tvdnhom.MaDoiNhom = madnhom;
            db.ThanhVien_DoiNhoms.InsertOnSubmit(tvdnhom);
            db.SubmitChanges();
        }

        public static void XoaThanhVienDoiNhom(string masv, int mdnh)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = from tvdnhom in db.ThanhVien_DoiNhoms
                        where tvdnhom.MaSV == masv && tvdnhom.MaDoiNhom == mdnh
                        select tvdnhom;
            if (sql.FirstOrDefault() != null)
            {
                db.ThanhVien_DoiNhoms.DeleteOnSubmit(sql.First());
                db.SubmitChanges();
            }
        }

        // Thêm, xóa vào bảng ThanhVien_HoatDong;
        public static void ThemThanhVienHoatDong(string masv, int mahd)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien_HoatDong tvhdong = new ThanhVien_HoatDong();
            tvhdong.MaSV = masv;
            tvhdong.MaHoatDong = mahd;
            db.ThanhVien_HoatDongs.InsertOnSubmit(tvhdong);
            db.SubmitChanges();
        }

        public static void XoaThanhVienHoatDong(string masv, int mahd)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = from tvhdong in db.ThanhVien_HoatDongs
                        where tvhdong.MaSV == masv && tvhdong.MaHoatDong == mahd
                        select tvhdong;
            if (sql.FirstOrDefault() != null)
            {
                db.ThanhVien_HoatDongs.DeleteOnSubmit(sql.First());
                db.SubmitChanges();
            }
        }

        // Thêm, xóa vào bảng ThanhVien_KhenThuong;
        public static void ThemThanhVienKhenThuong(string masv, int makt)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien_KhenThuong tvkthuong = new ThanhVien_KhenThuong();
            tvkthuong.MaSV = masv;
            tvkthuong.MaKhenThuong = makt;
            db.ThanhVien_KhenThuongs.InsertOnSubmit(tvkthuong);
            db.SubmitChanges();
                
        }

        public static void XoaThanhVienKhenThuong(string masv, int makt)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            
                var sql = from tvkthuong in db.ThanhVien_KhenThuongs
                          where tvkthuong.MaSV == masv && tvkthuong.MaKhenThuong == makt
                          select tvkthuong;
                if (sql.FirstOrDefault() != null)
                {
                    db.ThanhVien_KhenThuongs.DeleteOnSubmit(sql.First());
                    db.SubmitChanges();
                }
            
        }

        // Thêm, sửa, xóa vào bảng ChucVu
        public static void ThemChucVu(string macv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ChucVu cv = new ChucVu();
            cv.ChucVu1 = macv;
            db.ChucVus.InsertOnSubmit(cv);
            db.SubmitChanges();
        }

        // Sửa chức vụ;
        public static void SuaChucVu(string macv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            if (db.ChucVus.Where(q => q.ChucVu1 == macv) != null)
            {
                ChucVu cv = db.ChucVus.SingleOrDefault(q => q.ChucVu1 == macv);
                cv.ChucVu1 = macv;
                db.SubmitChanges();
            }
        }

        public static void XoaChucVu(string macv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ChucVu cv = db.ChucVus.FirstOrDefault(q => q.ChucVu1 == macv);
            if (cv != null)
            {
                db.ChucVus.DeleteOnSubmit(cv);
                db.SubmitChanges();
            }
        }

        // Lấy danh sách hoạt động của thành viên
        public static IQueryable<GroupManager.Resource.CSDL.HoatDong> DanhSachHoatDongThanhVien()
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = from hd in db.HoatDongs
                      join tvhd in db.ThanhVien_HoatDongs on hd.MaHoatDong equals tvhd.MaHoatDong
                      join tv in db.ThanhViens on tvhd.MaSV equals tv.MaSV
                      select hd;
            if (sql.Count() == 0)
                return null;
            return sql;
        }

        // Lấy danh sách khen thưởng của thành viên
        public static IQueryable<GroupManager.Resource.CSDL.KhenThuong> DanhSachKhenThuongThanhVien(string masv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = from kt in db.KhenThuongs
                      join tvkt in db.ThanhVien_KhenThuongs on kt.MaKhenThuong equals tvkt.MaKhenThuong
                      where tvkt.MaSV == masv
                      select kt;
            if (sql.Count() == 0)
                return null;
            return sql;
        }

        // Lấy danh sách thành viên
        public static IQueryable<GroupManager.Resource.CSDL.ThanhVien> DanhSachThanhVien()
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            var sql = db.ThanhViens.Select(q=>q);
            if (sql.Count() == 0)
                return null;
            return sql;
        }

        // Lấy thông tin sinh viên
        public static ThanhVien ThongTinSinhVien(string masv)
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            ThanhVien tv = db.ThanhViens.FirstOrDefault(q => q.MaSV == masv);
            return tv;
        }

        // Lấy danh sách quê quán
        public static IQueryable LayDanhSachQueQuan()
        {
            dbGroupManagerDataContext db = new dbGroupManagerDataContext();
            return db.ThanhViens.Select(p=> p.QueQuan).Distinct();
        }
    }
}